package com.wxd.service.impl;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang.StringUtils;

import com.wxd.dao.AccessDB;
import com.wxd.dao.ConnectionManager;
import com.wxd.service.ChartDataService;

public class ChartDataServiceImpl implements ChartDataService {
	public JSONArray getDevListGroupCompany() {
		JSONArray result = new JSONArray();

		String sql = "select 归属单位 from 设备信息 group by 归属单位";
		String devSql = "select 设备编号,设备名称 from 设备信息 where 归属单位=?";
		List companyList = AccessDB.queryForSql(sql,false);

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			pstm = conn.prepareStatement(devSql);

			for (int i = 0; i < companyList.size(); i++) {
				Map map = (Map) companyList.get(i);
				JSONObject companyJSON = new JSONObject();

				String company = map.get(1).toString();
				companyJSON.put("text", company);
				companyJSON.put("leaf", false);

				JSONArray leafArray = new JSONArray();
				pstm.setString(1, company);
				rs = pstm.executeQuery();
				while (rs.next()) {
					JSONObject leaf = new JSONObject();
					String devCode = rs.getString(1);
					leaf.put("id", devCode);
					leaf.put("text", devCode + " " + rs.getString(2));
					leaf.put("leaf", true);
					leafArray.add(leaf);
				}
				companyJSON.put("children", leafArray);

				result.add(companyJSON);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		} finally {
			AccessDB.close(conn, pstm, rs);
		}
		return result;
	}

	public StringBuffer singleDevChartData(String devCode, String startTime,
			String endTime) throws Exception {

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
		SimpleDateFormat sdf3 = new SimpleDateFormat("M.d");

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer result = new StringBuffer();
		try {
			result
					.append("<graph caption='"
							+ devCode
							+ "设备运行情况' baseFontSize='12' PYAxisName='second' SYAxisName='km'  showValues='1' numDivLines='4' formatNumberScale='0' decimalPrecision='0' anchorSides='10' anchorRadius='3' anchorBorderColor='009900'>");

			StringBuffer sb01 = new StringBuffer();
			StringBuffer sb04 = new StringBuffer();
			StringBuffer sb02 = new StringBuffer();
			StringBuffer sb03 = new StringBuffer();

			sb01.append("<dataset seriesName='运行时长' color='F6BD0F' showValues='0'>");
			sb04.append("<dataset seriesName='油耗' color='AFD8F8' showValues='0'>");
			sb02.append("<dataset seriesName='运行里程' color='8BBA00' showValues='0' parentYAxis='S'>");
			sb03.append("<categories>");

			String sql = "select sum(convert(float,运行时间)),sum(convert(float,运行里程)),sum(convert(numeric(10,1),油耗)) from 运行信息 where 设备编号=? and 停止时间>=? and 停止时间<?";
			pstm = conn.prepareStatement(sql);
			// 所有日期为空,显示近10天内的数据
			if (StringUtils.isEmpty(startTime) && StringUtils.isEmpty(endTime)) {
				Calendar instance = Calendar.getInstance();
				instance.set(Calendar.DATE, instance.get(Calendar.DATE) - 10);
				for (int i = 1; i <= 10; i++) {
					String minDate = sdf.format(instance.getTime());

					// 获得当天日期
					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devCode);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			} else if (StringUtils.isEmpty(endTime)) {
				Date date = sdf.parse(startTime);
				Calendar instance = Calendar.getInstance();
				long today = instance.getTimeInMillis();
				instance.setTime(date);

				while (true) {
					if (instance.getTimeInMillis() >= today) {
						// 到今天则退出
						break;
					}
					String minDate = sdf.format(instance.getTime());

					// 获得当天日期
					String todayDate = sdf3.format(instance.getTime());
					sb03.append("<category name='" + todayDate + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devCode);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			} else if (StringUtils.isEmpty(startTime)) {
				Date date = sdf.parse(endTime);
				Calendar instance = Calendar.getInstance();
				instance.setTime(date);

				instance.set(Calendar.DATE, instance.get(Calendar.DATE) - 9);
				for (int i = 1; i <= 10; i++) {
					String minDate = sdf.format(instance.getTime());

					// 获得当天日期
					String todayDate = sdf3.format(instance.getTime());
					sb03.append("<category name='" + todayDate + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devCode);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			} else {

				Date sDate = sdf.parse(startTime);
				Date eDate = sdf.parse(endTime);

				long stopTime = eDate.getTime();

				Calendar instance = Calendar.getInstance();
				instance.setTime(sDate);

				while (true) {
					if (instance.getTimeInMillis() > stopTime) {
						// 到今天则退出
						break;
					}
					String minDate = sdf.format(instance.getTime());

					// 获得当天日期
					String todayDate = sdf3.format(instance.getTime());
					sb03.append("<category name='" + todayDate + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devCode);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			}

			sb01.append("</dataset>");
			sb04.append("</dataset>");
			sb02.append("</dataset>");
			sb03.append("</categories>");

			result.append(sb03.toString());
			result.append(sb01.toString());
			result.append(sb04.toString());
			result.append(sb02.toString());

			result.append("</graph>");
		} catch(Exception e){e.printStackTrace();}finally {
			AccessDB.close(conn, pstm, rs);
		}
		return result;
	}

	public StringBuffer devSpiChartData(String devSpi, String startTime,
			String endTime) throws Exception {
		StringBuffer result = new StringBuffer();

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
		SimpleDateFormat sdf3 = new SimpleDateFormat("M.d");

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {

			result
					.append("<graph caption='"
							+ devSpi
							+ "规格设备运行情况' baseFontSize='12' PYAxisName='second' SYAxisName='km'  showValues='1' numDivLines='4' formatNumberScale='0' decimalPrecision='0' anchorSides='10' anchorRadius='3' anchorBorderColor='009900'>");

			StringBuffer sb01 = new StringBuffer();
			StringBuffer sb04 = new StringBuffer();
			StringBuffer sb02 = new StringBuffer();
			StringBuffer sb03 = new StringBuffer();

			sb01.append("<dataset seriesName='运行时长' color='F6BD0F' showValues='0'>");
			sb04.append("<dataset seriesName='油耗' color='AFD8F8' showValues='0'>");
			sb02.append("<dataset seriesName='运行里程' color='8BBA00' showValues='0' parentYAxis='S'>");
			sb03.append("<categories>");

			String sql = "select sum(convert(float,运行时间)),sum(convert(float,运行里程)),sum(convert(numeric(10,1),油耗)) from 运行信息 where 设备规格=? and 停止时间>=? and 停止时间<?";
			pstm = conn.prepareStatement(sql);
			// 所有日期为空,显示近10天内的数据
			if (StringUtils.isEmpty(startTime) && StringUtils.isEmpty(endTime)) {
				Calendar instance = Calendar.getInstance();
				instance.set(Calendar.DATE, instance.get(Calendar.DATE) - 10);
				for (int i = 1; i <= 10; i++) {
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devSpi);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			} else if (StringUtils.isEmpty(endTime)) {
				Date date = sdf.parse(startTime);
				Calendar instance = Calendar.getInstance();
				long today = instance.getTimeInMillis();
				instance.setTime(date);

				while (true) {
					if (instance.getTimeInMillis() >= today) {
						// 到今天则退出
						break;
					}
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devSpi);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			} else if (StringUtils.isEmpty(startTime)) {
				Date date = sdf.parse(endTime);
				Calendar instance = Calendar.getInstance();
				instance.setTime(date);

				instance.set(Calendar.DATE, instance.get(Calendar.DATE) - 9);
				for (int i = 1; i <= 10; i++) {
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devSpi);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}

						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}
						
						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			} else {
				Date sDate = sdf.parse(startTime);
				Date eDate = sdf.parse(endTime);

				long stopTime = eDate.getTime();

				Calendar instance = Calendar.getInstance();
				instance.setTime(sDate);

				while (true) {
					if (instance.getTimeInMillis() > stopTime) {
						// 到今天则退出
						break;
					}
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devSpi);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}
						
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}
						
						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
					}
				}
			}

			sb01.append("</dataset>");
			sb04.append("</dataset>");
			sb02.append("</dataset>");
			sb03.append("</categories>");

			result.append(sb03.toString());
			result.append(sb01.toString());
			result.append(sb04.toString());
			result.append(sb02.toString());

			result.append("</graph>");

		} finally {
			AccessDB.close(conn, pstm, rs);
		}
		return result;
	}

	public StringBuffer devUnitChartData(String devUnit, String startTime,
			String endTime) throws Exception {
		StringBuffer result = new StringBuffer();

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
		SimpleDateFormat sdf3 = new SimpleDateFormat("M.d");

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			result
					.append("<graph caption='"
							+ devUnit
							+ "设备运行情况' baseFontSize='12' PYAxisName='baseUnit' SYAxisName='km'  showValues='1' numDivLines='4' formatNumberScale='0' decimalPrecision='0' anchorSides='10' anchorRadius='3' anchorBorderColor='009900'>");

			StringBuffer sb01 = new StringBuffer();
			StringBuffer sb04 = new StringBuffer();
			StringBuffer sb02 = new StringBuffer();
			StringBuffer sb03 = new StringBuffer();

			sb01.append("<dataset seriesName='运行时长' color='F6BD0F' showValues='0'>");
			sb04.append("<dataset seriesName='油耗' color='AFD8F8' showValues='0'>");
			sb02
					.append("<dataset seriesName='运行里程' color='8BBA00' showValues='0' parentYAxis='S'>");
			sb03.append("<categories>");

			String sql = "select sum(convert(float,运行时间)),sum(convert(float,运行里程)),sum(convert(numeric(10,1),油耗)) from 运行信息 where 归属单位=? and 停止时间>=? and 停止时间<?";
			pstm = conn.prepareStatement(sql);
			// 所有日期为空,显示近10天内的数据
			if (StringUtils.isEmpty(startTime) && StringUtils.isEmpty(endTime)) {
				Calendar instance = Calendar.getInstance();
				instance.set(Calendar.DATE, instance.get(Calendar.DATE) - 10);
				for (int i = 1; i <= 10; i++) {
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devUnit);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
						
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}
					}
				}
			} else if (StringUtils.isEmpty(endTime)) {
				Date date = sdf.parse(startTime);
				Calendar instance = Calendar.getInstance();
				long today = instance.getTimeInMillis();
				instance.setTime(date);

				while (true) {
					if (instance.getTimeInMillis() >= today) {
						// 到今天则退出
						break;
					}
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devUnit);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}
					}
				}
			} else if (StringUtils.isEmpty(startTime)) {
				Date date = sdf.parse(endTime);
				Calendar instance = Calendar.getInstance();
				instance.setTime(date);

				instance.set(Calendar.DATE, instance.get(Calendar.DATE) - 9);
				for (int i = 1; i <= 10; i++) {
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devUnit);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}
					}
				}
			} else {

				Date sDate = sdf.parse(startTime);
				Date eDate = sdf.parse(endTime);

				long stopTime = eDate.getTime();

				Calendar instance = Calendar.getInstance();
				instance.setTime(sDate);

				while (true) {
					if (instance.getTimeInMillis() > stopTime) {
						// 到今天则退出
						break;
					}
					String minDate = sdf.format(instance.getTime());

					sb03.append("<category name='"
							+ sdf3.format(instance.getTime()) + "'/>");

					instance
							.set(Calendar.DATE, instance.get(Calendar.DATE) + 1);
					String maxDate = sdf.format(instance.getTime());

					pstm.setString(1, devUnit);
					pstm.setString(2, minDate);
					pstm.setString(3, maxDate);

					rs = pstm.executeQuery();
					while (rs.next()) {
						String t = rs.getString(1);
						String j = rs.getString(2);
						String y = rs.getString(3);
						if (t == null) {
							sb01.append("<set  value='0' />");
						} else {
							sb01.append("<set  value='" + t + "' />");
						}

						if (j == null) {
							sb02.append("<set  value='0' />");
						} else {
							sb02.append("<set  value='" + j + "' />");
						}
						if (y == null) {
							sb04.append("<set  value='0' />");
						} else {
							sb04.append("<set  value='" + y + "' />");
						}
					}
				}
			}

			sb01.append("</dataset>");
			sb02.append("</dataset>");
			sb04.append("</dataset>");
			sb03.append("</categories>");

			result.append(sb03.toString());
			result.append(sb01.toString());
			result.append(sb04.toString());
			result.append(sb02.toString());

			result.append("</graph>");
		} finally {
			AccessDB.close(conn, pstm, rs);
		}
		return result;
	}

	public StringBuffer unitRunTimeComparechartDate(int year) throws Exception {
		StringBuffer result = new StringBuffer();
		result
				.append("<graph baseFontSize='12' yaxisname='second'  hovercapbg='DEDEBE' hovercapborder='889E6D' rotateNames='0' yAxisMaxValue='100' numdivlines='9' divLineColor='CCCCCC' divLineAlpha='80' decimalPrecision='0' showAlternateHGridColor='1' AlternateHGridAlpha='30' AlternateHGridColor='CCCCCC' caption='设备归属单位运行时间对比图'>");
		StringBuffer categories = new StringBuffer();
		categories
				.append("<categories font='Arial' fontSize='11' fontColor='000000'>");

		String companySql = "select 归属单位 from 设备信息";
		List companyList = AccessDB.queryForSql(companySql,false);

		List<String> companyName = new ArrayList<String>();
		for (int x = 0; x < companyList.size(); x++) {
			Map map = (Map) companyList.get(x);
			companyName.add(map.get(1).toString());
		}

		StringBuffer[] sb = new StringBuffer[companyName.size()];

		for (int n = 0; n < sb.length; n++) {
			sb[n] = new StringBuffer();
			sb[n].append("<dataset seriesname='" + companyName.get(n)
					+ "' color='"+COLORS[n]+"'>");
		}

		String sql = "select 归属单位,sum(convert(float,运行时间)) from 运行信息 where 停止时间>=? and 停止时间<? group by 归属单位";

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-01 00:00:00");
		Calendar instance = Calendar.getInstance();
		instance.set(Calendar.DATE, 1);
		instance.set(Calendar.YEAR, year);

		for (int i = 1; i < 13; i++) {
			categories.append("<category name='" + i + "月'/>");

			instance.set(Calendar.MONTH, i - 1);
			String minTime = sdf.format(instance.getTime());
			instance.set(Calendar.MONTH, i);
			String maxTime = sdf.format(instance.getTime());

			pstm.setString(1, minTime);
			pstm.setString(2, maxTime);

			rs = pstm.executeQuery();

			// 标记哪个companyName的索引没有被加上字符串
			int[] flag = new int[companyName.size()];
			for (int y = 0; y < flag.length; y++) {
				flag[y] = 0;
			}

			while (rs.next()) {
				String str = rs.getString(1);
				if (companyName.contains(str)) {
					String time = rs.getString(2);
					int indexOf = companyName.indexOf(str);
					if (time != null) {
						sb[indexOf].append("<set value='" + time + "'/>");
						flag[indexOf] = 1;
					}
				}
			}

			for (int y = 0; y < flag.length; y++) {
				if (flag[y] == 0) {
					sb[y].append("<set value='0'/>");
				}
			}

		}

		categories.append("</categories>");
		result.append(categories.toString());
		for (int n = 0; n < sb.length; n++) {
			sb[n].append("</dataset>");
			result.append(sb[n].toString());
		}

		result.append("</graph>");
		return result;
	}

	public StringBuffer unitRunDistanceComparechartDate(int year)
			throws Exception {
		StringBuffer result = new StringBuffer();
		result
				.append("<graph baseFontSize='12' yaxisname='second'  hovercapbg='DEDEBE' hovercapborder='889E6D' rotateNames='0' yAxisMaxValue='100' numdivlines='9' divLineColor='CCCCCC' divLineAlpha='80' decimalPrecision='0' showAlternateHGridColor='1' AlternateHGridAlpha='30' AlternateHGridColor='CCCCCC' caption='设备归属单位运行里程对比图'>");

		StringBuffer categories = new StringBuffer();
		categories
				.append("<categories font='Arial' fontSize='11' fontColor='000000'>");

		String companySql = "select 归属单位 from 设备信息";
		List companyList = AccessDB.queryForSql(companySql,false);

		List companyName = new ArrayList();
		for (int x = 0; x < companyList.size(); x++) {
			Map map = (Map) companyList.get(x);
			companyName.add(map.get(1));
		}

		StringBuffer[] sb = new StringBuffer[companyName.size()];

		for (int n = 0; n < sb.length; n++) {
			sb[n] = new StringBuffer();
			sb[n].append("<dataset seriesname='" + companyName.get(n)
					+ "' color='"+COLORS[n]+"'>");
		}

		String sql = "select 归属单位,sum(convert(float,运行里程)) from 运行信息 where 停止时间>=? and 停止时间<? group by 归属单位";

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-01 00:00:00");
		Calendar instance = Calendar.getInstance();
		instance.set(Calendar.DATE, 1);
		instance.set(Calendar.YEAR, year);

		for (int i = 1; i < 13; i++) {
			categories.append("<category name='" + i + "月'/>");

			instance.set(Calendar.MONTH, i - 1);
			String minTime = sdf.format(instance.getTime());
			instance.set(Calendar.MONTH, i);
			String maxTime = sdf.format(instance.getTime());

			pstm.setString(1, minTime);
			pstm.setString(2, maxTime);

			rs = pstm.executeQuery();

			// 标记哪个companyName的索引没有被加上字符串
			int[] flag = new int[companyName.size()];
			for (int y = 0; y < flag.length; y++) {
				flag[y] = 0;
			}

			while (rs.next()) {
				String str = rs.getString(1);
				if (companyName.contains(str)) {
					String time = rs.getString(2);
					int indexOf = companyName.indexOf(str);
					if (time != null) {
						sb[indexOf].append("<set value='" + time + "'/>");
						flag[indexOf] = 1;
					}
				}
			}

			for (int y = 0; y < flag.length; y++) {
				if (flag[y] == 0) {
					sb[y].append("<set value='0'/>");
				}
			}

		}

		categories.append("</categories>");
		result.append(categories.toString());
		for (int n = 0; n < sb.length; n++) {
			sb[n].append("</dataset>");
			result.append(sb[n].toString());
		}

		result.append("</graph>");
		return result;
	}

	public StringBuffer spiRunDistanceComparechartDate(int year)
			throws Exception {
		StringBuffer result = new StringBuffer();
		result
				.append("<graph baseFontSize='12' yaxisname='second'  hovercapbg='DEDEBE' hovercapborder='889E6D' rotateNames='0' yAxisMaxValue='100' numdivlines='9' divLineColor='CCCCCC' divLineAlpha='80' decimalPrecision='0' showAlternateHGridColor='1' AlternateHGridAlpha='30' AlternateHGridColor='CCCCCC' caption='设备规格运行里程对比图'>");
		StringBuffer categories = new StringBuffer();
		categories
				.append("<categories font='Arial' fontSize='11' fontColor='000000'>");

		String companySql = "select 设备规格 from 设备信息";
		List companyList = AccessDB.queryForSql(companySql,false);

		List companyName = new ArrayList();
		for (int x = 0; x < companyList.size(); x++) {
			Map map = (Map) companyList.get(x);
			companyName.add(map.get(1));
		}

		StringBuffer[] sb = new StringBuffer[companyName.size()];

		for (int n = 0; n < sb.length; n++) {
			sb[n] = new StringBuffer();
			sb[n].append("<dataset seriesname='" + companyName.get(n)
					+ "' color='"+COLORS[n]+"'>");
		}

		String sql = "select 设备规格,sum(convert(float,运行里程)) from 运行信息 where 停止时间>=? and 停止时间<? group by 设备规格";

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-01 00:00:00");
		Calendar instance = Calendar.getInstance();
		instance.set(Calendar.DATE, 1);
		instance.set(Calendar.YEAR, year);

		for (int i = 1; i < 13; i++) {
			categories.append("<category name='" + i + "月'/>");

			instance.set(Calendar.MONTH, i - 1);
			String minTime = sdf.format(instance.getTime());
			instance.set(Calendar.MONTH, i);
			String maxTime = sdf.format(instance.getTime());

			pstm.setString(1, minTime);
			pstm.setString(2, maxTime);

			rs = pstm.executeQuery();

			// 标记哪个companyName的索引没有被加上字符串
			int[] flag = new int[companyName.size()];
			for (int y = 0; y < flag.length; y++) {
				flag[y] = 0;
			}

			while (rs.next()) {
				String str = rs.getString(1);
				if (companyName.contains(str)) {
					String time = rs.getString(2);
					int indexOf = companyName.indexOf(str);
					if (time != null) {
						sb[indexOf].append("<set value='" + time + "'/>");
						flag[indexOf] = 1;
					}
				}
			}

			for (int y = 0; y < flag.length; y++) {
				if (flag[y] == 0) {
					sb[y].append("<set value='0'/>");
				}
			}

		}

		categories.append("</categories>");
		result.append(categories.toString());
		for (int n = 0; n < sb.length; n++) {
			sb[n].append("</dataset>");
			result.append(sb[n].toString());
		}

		result.append("</graph>");
		return result;
	}

	public StringBuffer spiRunTimeComparechartDate(int year) throws Exception {
		StringBuffer result = new StringBuffer();
		result
				.append("<graph baseFontSize='12' yaxisname='second'  hovercapbg='DEDEBE' hovercapborder='889E6D' rotateNames='0' yAxisMaxValue='100' numdivlines='9' divLineColor='CCCCCC' divLineAlpha='80' decimalPrecision='0' showAlternateHGridColor='1' AlternateHGridAlpha='30' AlternateHGridColor='CCCCCC' caption='设备规格运行时间对比图'>");
		StringBuffer categories = new StringBuffer();
		categories
				.append("<categories font='Arial' fontSize='11' fontColor='000000'>");

		String companySql = "select 设备规格 from 设备信息";
		List companyList = AccessDB.queryForSql(companySql,false);

		List companyName = new ArrayList();
		for (int x = 0; x < companyList.size(); x++) {
			Map map = (Map) companyList.get(x);
			companyName.add(map.get(1));
		}

		StringBuffer[] sb = new StringBuffer[companyName.size()];

		for (int n = 0; n < sb.length; n++) {
			sb[n] = new StringBuffer();
			sb[n].append("<dataset seriesname='" + companyName.get(n)
					+ "' color='"+COLORS[n]+"'>");
		}

		String sql = "select 设备规格,sum(convert(float,运行时间)) from 运行信息 where 停止时间>=? and 停止时间<? group by 设备规格";

		Connection conn = ConnectionManager.getInstance().getConnection();
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-01 00:00:00");
		Calendar instance = Calendar.getInstance();
		instance.set(Calendar.DATE, 1);
		instance.set(Calendar.YEAR, year);

		for (int i = 1; i < 13; i++) {
			categories.append("<category name='" + i + "月'/>");

			instance.set(Calendar.MONTH, i - 1);
			String minTime = sdf.format(instance.getTime());
			instance.set(Calendar.MONTH, i);
			String maxTime = sdf.format(instance.getTime());

			pstm.setString(1, minTime);
			pstm.setString(2, maxTime);

			rs = pstm.executeQuery();

			// 标记哪个companyName的索引没有被加上字符串
			int[] flag = new int[companyName.size()];
			for (int y = 0; y < flag.length; y++) {
				flag[y] = 0;
			}

			while (rs.next()) {
				String str = rs.getString(1);
				if (companyName.contains(str)) {
					String time = rs.getString(2);
					int indexOf = companyName.indexOf(str);
					if (time != null) {
						sb[indexOf].append("<set value='" + time + "'/>");
						flag[indexOf] = 1;
					}
				}
			}

			for (int y = 0; y < flag.length; y++) {
				if (flag[y] == 0) {
					sb[y].append("<set value='0'/>");
				}
			}

		}

		categories.append("</categories>");
		result.append(categories.toString());
		for (int n = 0; n < sb.length; n++) {
			sb[n].append("</dataset>");
			result.append(sb[n].toString());
		}

		result.append("</graph>");
		return result;
	}
	
	public StringBuffer oilWearChartData(String devCode){
		String sql = "select top 100 停止时间,油耗,当前温度 from 运行信息 where 设备编号='"+devCode+"'";
		
		List queryForSql = AccessDB.queryForSql(sql, false);
		StringBuffer result = new StringBuffer();
		
		result.append("<graph caption='"+devCode+"设备油耗&温度曲线'  hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='0' showvalues='0' numdivlines='3' numVdivlines='0'  rotateNames='1'>");
		
		StringBuffer sb01 = new StringBuffer();
		StringBuffer sb02 = new StringBuffer();
		StringBuffer sb03 = new StringBuffer();
		
		sb01.append("<categories>");
		sb02.append("<dataset seriesName='油耗' color='1D8BD1' anchorBorderColor='1D8BD1' anchorBgColor='1D8BD1'>");
		sb03.append("<dataset seriesName='温度' color='F1683C' anchorBorderColor='F1683C' anchorBgColor='F1683C'>");
		
		if(queryForSql!=null&&queryForSql.size()>0){
			for(int n=0;n<queryForSql.size();n++){
				Map map = (Map)queryForSql.get(n);
				
				sb01.append("<category name='"+map.get(1).toString()+"' />");
				sb02.append("<set value='"+(map.get(2)!=null?map.get(2).toString():"0")+"' />");
				sb03.append("<set value='"+(map.get(3)!=null?map.get(3).toString():"0")+"' />");
			}
		}
		
		
		
		sb01.append("</categories>");
		sb02.append("</dataset>");
		sb03.append("</dataset>");
		
		result.append(sb01.toString());
		result.append(sb02.toString());
		result.append(sb03.toString());
		result.append("</graph>");
		
		return result;
	}
	
	
	private final static String[] COLORS = new String[]{"AFD8F8","F6BD0F","8BBA00","FF8E46","008E8E","D64646","8E468E","588526","B3AA00","008ED6","9D080D","A186BE"};
	
	//设备信息表
	public List<Map> get_JK_baseInfo(String devCode) {
		List<Map> result = new ArrayList<Map>();
		String sql1 = "select 设备名称,设备规格,归属单位,投产日期,操作员 from 设备信息 where 设备编号='"+devCode+"'";
		String sql2 = "select 上次维护时间,上次维护内容,总运行时间,总运行里程 from 累计信息 where 设备编号='"+devCode+"'";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				map.put(3, rs.getString(3));
				map.put(4, rs.getString(4));
				map.put(5, rs.getString(5));
				result.add(map);
			}else{
				result.add(null);
			}
			
			rs = stmt.executeQuery(sql2);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				map.put(3, rs.getString(3));
				map.put(4, rs.getString(4));
				result.add(map);
			}else{
				result.add(null);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}finally{
			AccessDB.close(conn, stmt, rs);
		}
		return result;
	}
	
	
	public List<Map> get_JK_jianBao(String devCode, String startDate,
			String endDate) {
		List<Map> result  = new ArrayList<Map>();
		String sql1= "select sum(convert(float,运行时间)),sum(convert(float,运行里程)),sum(convert(numeric(10,1),油耗)),avg(convert(integer,当前温度)) from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"'";
		//最高工时
		String sql2 ="select top 1 convert(integer,运行时间),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(integer,运行时间) desc,停止时间 desc";
		//最低工时
		String sql3 ="select top 1 convert(integer,运行时间),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(integer,运行时间) asc,停止时间 desc";
		//最高里程
		String sql4 ="select top 1 convert(integer,运行里程),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(integer,运行里程) desc,停止时间 desc";
		//最低里程
		String sql5 ="select top 1 convert(integer,运行里程),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(integer,运行里程) asc,停止时间 desc";
		//最高油耗
		String sql6 ="select top 1 convert(numeric(10,1),油耗),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(numeric(10,1),油耗) desc,停止时间 desc";
		//最低油耗
		String sql7 ="select top 1 convert(numeric(10,1),油耗),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(numeric(10,1),油耗) asc,停止时间 desc";
		//最高温度
		String sql8 ="select top 1 convert(integer,当前温度),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(integer,当前温度) desc,停止时间 desc";
		//最低温度
		String sql9 ="select top 1 convert(integer,当前温度),停止时间 from 运行信息 where 设备编号='"+devCode+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' order by convert(integer,当前温度) asc,停止时间 desc";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				map.put(3, rs.getString(3));
				map.put(4, rs.getString(4));
				result.add(map);
			}else{
				result.add(null);
			}
			rs = stmt.executeQuery(sql2);
			
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			
			
			rs = stmt.executeQuery(sql3);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			
			rs = stmt.executeQuery(sql4);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			rs = stmt.executeQuery(sql5);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			rs = stmt.executeQuery(sql6);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			rs = stmt.executeQuery(sql7);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			rs = stmt.executeQuery(sql8);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			rs = stmt.executeQuery(sql9);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				result.add(map);
			}else{
				result.add(null);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}finally{
			AccessDB.close(conn, stmt, rs);
		}
		return result;
	}
	
	
	public List<Map> get_JK_sbdb(String devCode, String startDate,
			String endDate,int dayNum) {
		List<Map> result = new ArrayList<Map>();
		String devSpi = null;
		String sql1 = "select 设备规格 from 设备信息 where 设备编号='"+devCode+"'";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			//获得设备规格
			if(rs.next()){
				devSpi = rs.getString(1);
			}else{
				return result;
			}
			String tempSql = "select count(c) from (select count(*) as c from 运行信息 where 设备规格='"+devSpi+"' and 停止时间<='"+endDate+"' and 停止时间>='"+startDate+"' group by 设备编号) as t";
			//获得设备总个数
			int devCount =1;
			rs = stmt.executeQuery(tempSql);
			if(rs.next()){
				devCount = rs.getInt(1);
			}
			
			String sql2 = "select sum(convert(float,运行时间))/"+dayNum+"/"+devCount+",sum(convert(float,运行里程))/"+dayNum+"/"+devCount+",sum(convert(numeric(10,1),油耗))/"+dayNum+"/"+devCount+",sum(convert(integer,当前温度))/"+dayNum+"/count(1) from 运行信息 where 设备规格='"+devSpi+"' and 停止时间<='"+endDate+"' and 停止时间>='"+startDate+"'";
			//获得设备的平均
			rs = stmt.executeQuery(sql2);
			if(rs.next()){
				Map map = new HashedMap();
				map.put(1, rs.getString(1));
				map.put(2, rs.getString(2));
				map.put(3, rs.getString(3));
				map.put(4, rs.getString(4));
				result.add(map);
			}else{
				result.add(null);
			}
			
			Map paiMing = new HashedMap();
			String sql3 = "select * from (select 设备编号,sum(convert(float,运行时间))/"+dayNum+" as runTime from 运行信息  where 设备规格='"+devSpi+"' and 停止时间<='"+endDate+"' and 停止时间>='"+startDate+"' group by 设备编号 ) as t order by runTime desc";
			rs = stmt.executeQuery(sql3);
			int gsInt = -1;
			int num = 1;
			while(rs.next()){
				if(devCode.equals(rs.getString(1))){
					gsInt = num;
					paiMing.put("gsInt", gsInt);
					break;
				}else{
					num++;
				}
			}
			
			
			
			
			String sql4 = "select * from (select 设备编号,sum(convert(float,运行里程))/"+dayNum+" as runDistance from 运行信息  where 设备规格='"+devSpi+"' and 停止时间<='"+endDate+"' and 停止时间>='"+startDate+"' group by 设备编号 ) as t order by runDistance desc";
			rs = stmt.executeQuery(sql4);
			int lcInt = -1;
			num = 1;
			while(rs.next()){
				if(devCode.equals(rs.getString(1))){
					lcInt = num;
					paiMing.put("lcInt", lcInt);
					break;
				}else{
					num++;
				}
			}
			
			String sql5 = "select * from (select 设备编号,sum(convert(numeric(10,1),油耗))/"+dayNum+" as youhao from 运行信息  where 设备规格='"+devSpi+"' and 停止时间<='"+endDate+"' and 停止时间>='"+startDate+"' group by 设备编号 ) as t order by youhao desc";
			rs = stmt.executeQuery(sql5);
			int yhInt = -1;
			num = 1;
			while(rs.next()){
				if(devCode.equals(rs.getString(1))){
					yhInt = num;
					paiMing.put("yhInt", yhInt);
					break;
				}else{
					num++;
				}
			}
			
			
			String sql6 = "select * from (select 设备编号,sum(convert(integer,当前温度))/"+dayNum+"/count(1) as wendu from 运行信息  where 设备规格='"+devSpi+"' and 停止时间<='"+endDate+"' and 停止时间>='"+startDate+"' group by 设备编号 ) as t order by wendu desc";
			rs = stmt.executeQuery(sql6);
			int wdInt = -1;
			num = 1;
			while(rs.next()){
				System.out.println(rs.getString(2));
				if(devCode.equals(rs.getString(1))){
					wdInt = num;
					paiMing.put("wdInt", wdInt);
					break;
				}else{
					num++;
				}
			}
			
			result.add(paiMing);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}finally{
			AccessDB.close(conn, stmt, rs);
		}
		return result;
	}
	
	
	public List<Map> get_JK_sbpf(String devCode,String startDate,String endDate){
		List<Map> result = new ArrayList<Map>();
		String devSpi = null;
		String sql1 = "select 设备规格 from 设备信息 where 设备编号='"+devCode+"'";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			//获得设备规格
			if(rs.next()){
				devSpi = rs.getString(1);
			}else{
				return result;
			}
			String sql = "select * from (select 设备编号,(case when sum(convert(numeric(10,1),油耗))=0 then 0 else (sum(convert(float,运行时间))+sum(convert(float,运行里程)))/sum(convert(numeric(10,1),油耗)) end) as nh from 运行信息 where  设备规格='"+devSpi+"' and 停止时间>='"+startDate+"' and 停止时间<='"+endDate+"' group by 设备编号 ) as t order by nh desc";
			rs = stmt.executeQuery(sql);
			int i = 1;
			while(rs.next()){
				if(devCode.equals(rs.getString(1))){
					Map map =new HashedMap();
					map.put(1, i);
					map.put(2, rs.getString(2));
					result.add(map);
					break;
				}
				i++;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			AccessDB.close(conn, stmt, rs);
		}
		return result;
	}
}
